﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using GBH.Models;
using System.IO;
using GBH.Models.Dto;
using System.Globalization;
using OfficeOpenXml;
using System.Web.Configuration;
using GBH.Models.Dao;

namespace GBH.Controllers
{
    public class ProductApiController : ApiController
    {
        // Post api/ProductApi/PostAllproducts
        public IEnumerable<material> PostAllproducts()
        {
            using (var db = new GBHEntities())
            {
                return db.materials.AsEnumerable();
            }
        }

        // Get api/ProductApi/PostUploadSellOut
        public List<sell_out> PostUploadSellOut()
        {
            using (var db = new GBHEntities())
            {
                var SellOuts = (from sellout in db.sell_out
                                select sellout).ToList();
                return SellOuts;
            }
        }

        // Post api/ProductApi/PostHistorySellOutUpload
        public List<FileUploadHistory> PostHistorySellOutUpload()
        {
            using (var db = new GBHEntities())
            {
                var SellOutsHistory = (from history in db.FileUploadHistories
                                       where history.UploadType == 2
                                       select history).ToList();
                return SellOutsHistory;
            }
        }

        // Post api/ProductApi/PostHistoryStockUpload
        public List<FileUploadHistory> PostHistoryStockUpload()
        {
            using (var db = new GBHEntities())
            {
                var StockHistory = (from history in db.FileUploadHistories
                                    where history.UploadType == 1
                                    select history).ToList();
                return StockHistory;
            }
        }

        // Get api/ProductApi/PostUploadStockBalance
        public List<stock> PostUploadStockBalance()
        {
            using (var db = new GBHEntities())
            {
                var Stocks = (from stocks in db.stocks
                              select stocks).ToList();
                return Stocks;
            }
        }

        public bool GetConfirmDraftPi(string branchCode)
        {
            using (var db = new GBHEntities())
            {
                if (!String.IsNullOrEmpty(branchCode))
                {
                    db.Database.ExecuteSqlCommand("update draftPi set isEnable = 'true' where branchCode = '" + branchCode.Trim() + "'");
                    return true;
                }
                return false;
            }
        }

        public bool GetCreateExportPi(string branchCode)
        {
            using (var db = new GBHEntities())
            {
                if (!String.IsNullOrEmpty(branchCode))
                {
                    var draft = (from d in db.draftPis
                                where d.branchCode == branchCode && d.isEnable == true
                                select d).ToList();
                    string filename = string.Empty;
                    int exportId = 0;
                    int exportVersion = 1;
                    var lastExport = (from export in db.ExportPis
                                      where export.BranchCode == branchCode
                                      select export).OrderByDescending(o => o.CreateDate).FirstOrDefault();
                    if (lastExport != null)
                    {
                        exportVersion = lastExport.ExportVersion + 1 ?? 1;
                    }
                    var branchProvider = new GBH.Models.Dao.BranchDao();
                    var branchName = branchProvider.GetBranchByBranchCode(branchCode) != null ?branchProvider.GetBranchByBranchCode(branchCode).mprovdesc : "";

                    var newExport = new ExportPi()
                    {
                        BranchCode = branchCode,
                        BranchName =  branchName,
                        CreateDate = DateTime.Now,
                        ExportVersion = exportVersion,
                        FileName = string.Format("{0}-PI{1}.xlsx", branchCode, exportVersion.ToString().PadLeft(5,'0'))
                    };

                    db.ExportPis.Add(newExport);
                    db.SaveChanges();
                    exportId = newExport.Id;
                    List<ExportPiDetail> exportDetails = new List<ExportPiDetail>();
                    foreach (var d in draft)
                    {
                        exportDetails.Add(new ExportPiDetail()
                        {
                            branchCode = d.branchCode,
                            createDate = DateTime.Now,
                            filename = filename,
                            ExportId = exportId,
                            isEnable = false,
                            isExport = false,
                            nawaProductId = d.nawaProductId,
                            nawaProductName = d.nawaProductName,
                            productId = d.productId,
                            productName = d.productName,
                            qty = d.qty,
                            type = d.type,
                           
                        });
                    }
                    db.ExportPiDetails.AddRange(exportDetails);
                    db.SaveChanges();
                    return true;
                }
                return false;
            }
        }

        public List<ExportPi> GetAllExportPi()
        {
            using (var db = new GBHEntities())
            {
                var allexport = (from export in db.ExportPis
                                group export by export.BranchCode into g
                                select g.OrderByDescending(p => p.Id).FirstOrDefault()).ToList();
                //var allLastestExport = from e in db.ExportPis
                //                       where allexport.Contains(e.Id)
               
                return allexport;
            }
        }

        public HttpResponseMessage GetDownloadExport(int exportId)
        {

            var exportProvider = new GBH.Models.Dao.ExportPiDao();
            var exportDetailProvider = new GBH.Models.Dao.ExportDetailPiDao();
            var filename = exportProvider.GetExportPiById(exportId) != null ? exportProvider.GetExportPiById(exportId).FileName : "Pi.xlsx" ;
           // var details = exportDetailProvider.GetExportPi(exportId);
            var dt = new DataTable();
            dt.Columns.Add("ชื่อย่อสาขา");
            dt.Columns.Add("รหัสสินค้า");
            dt.Columns.Add("ชื่อสินค้า");
            dt.Columns.Add("หน่วยนับ");
            dt.Columns.Add("จำนวนเสนอสั่งซื้อ");

            using (var db = new GBHEntities())
            {
                var export = from e in db.ExportPiDetails
                             join m in db.stocks on e.productId equals m.product_id
                             where e.ExportId == exportId && e.qty > 0 && m.location_id == e.branchCode
                             select new {
                                 productId = e.productId,
                                 productName = e.productName,
                                 branchCode = e.branchCode,
                                 qty = e.qty,
                                 UnitTypeName = m.unit_type_name
                             };
                foreach (var d in export)
                {
                    dt.Rows.Add(d.branchCode, d.productId, d.productName, d.UnitTypeName, d.qty);
                }
            }

            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("PI");
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK);
                response.Content = new StreamContent(new MemoryStream(pck.GetAsByteArray()));
                response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.Add("content-disposition", "attachment;  filename=" + filename);
                GetUpdateExportPi(exportId);
                return response;
            }
        }


        public bool GetUpdateExportPi(int exportId)
        {
            using (var db = new GBHEntities())
            {
                var export = db.ExportPis.Where(e=>e.Id == exportId).FirstOrDefault();
                if (export != null)
                {
                    export.ExportDate = DateTime.Now;
                    db.SaveChanges();
                    return true;
                }
                return false;
            }
        }

        // Get api/ProductApi/PostCalSellout
        public bool PostCalSellout(string[] branchIds)
        {
            try
            {
                using (var db = new GBHEntities())
                {
                    var piOrders = new List<PiOrder>();
                    var branchProvider = new BranchDao();
                    var matProvider = new GBHNawaData.Dao.MaterialDao();
                    foreach (var branchid in branchIds)
                    {
                        try
                        {
                            var Stocks = (from stocks in db.stocks
                                          join mat in db.materials on stocks.product_id equals mat.npibarcode
                                          where stocks.location_id == branchid
                                          select new
                                          {
                                              s = stocks,
                                              m = mat
                                          }).ToList();
                            var minmax = (from minMax in db.daysellMinMaxes
                                          where minMax.location_id == branchid
                                          select minMax).FirstOrDefault();
                            var sellout = (from sellOut in db.sell_out
                                           where sellOut.location_id == branchid
                                           select sellOut).ToList();
                            var cusno = branchProvider.GetCusNoByBranchId(branchid);
                            foreach (var stock in Stocks)
                            {

                                var piOrder = new PiOrder();
                                var giProvider = new GBHNawaData.Dao.GiDao();
                                var matNawaProvider = new GBHNawaData.Dao.MaterialDao();
                                var sellbyMat = from sell in sellout
                                                where sell.product_id == stock.s.product_id
                                                select sell;
                                //string nawaMatNo = matProvider.GetMaterialByNpiBarcode(stock.m.mmatnoproduct_id) != null ? matProvider.GetMaterialByNpiBarcode(stock.product_id).mmatno : "";

                                if (stock.s.product_id == "8858721535675" && branchid == "WK")
                                {
                                    var k = "break";
                                }
                                var qtyDelay = giProvider.getQtyDelay(cusno, stock.m.mmatno);
                                var pi = new CallPIPerProductPerLocation(minmax);
                                var converUnit =  matNawaProvider.GetConvertUnit(stock.m.mmatno);
                                pi.RollingStart = DateTime.Today; // DateTime.Parse("16/9/2557", new CultureInfo("th-TH"));
                                pi.RollingOutDay = WebConfigurationManager.AppSettings["RollingOutDay"] != null ? Convert.ToInt32(WebConfigurationManager.AppSettings["RollingOutDay"]) : 60;
                                pi.Sellouts = sellbyMat.ToList();
                                pi.Stock = stock.s;
                                pi.QtyDelay = qtyDelay;
                                if (converUnit != null)
                                {
                                    pi.ConverUnit = Convert.ToInt32(converUnit.mbaseconvalue);
                                }

                                piOrder.PiQTY = pi.CalPi();
                                piOrder.ProductID = stock.s.product_id;
                                piOrder.ProductName = stock.s.product_name;
                                piOrder.NawaMatNo = stock.m.mmatno;
                                piOrder.NawaMatName = stock.m.mnamethai;
                                piOrder.LocationID = branchid;
                                piOrders.Add(piOrder);
                            }
                        }
                        catch (Exception e)
                        {

                            var k = e.Message + "branchid = " + branchid;
                            return false;
                        }
                    }
                    //string[] allMeanCode = piOrders.Select(m => m.ProductID).Distinct().ToArray();
                    //var nawaProduct = matProvider.GetAllMaterialByMeanCode(allMeanCode);

                    //foreach (var pi in piOrders)
                    //{
                    //    foreach (var p in nawaProduct)
                    //    {
                    //        if (pi.ProductID == p.meancode)
                    //        {
                    //            pi.NawaMatNo = p.mmatno;
                    //            pi.NawaMatName = p.mmatthai;
                    //        }
                    //    }
                    //}
                    var result = piOrders.GroupBy(pi => pi.LocationID, (key, group) => new GroupPiLocation()
                    {
                        Location = key,
                        PiOrder = group
                    });

                    var alldrafts = new List<draftPi>();
                    foreach (var piByBranch in result)
                    {
                        foreach (var pi in piByBranch.PiOrder)
                        {
                            alldrafts.Add(new draftPi()
                            {
                                branchCode = pi.LocationID ?? "",
                                nawaProductId = pi.NawaMatNo ?? "",
                                nawaProductName = pi.NawaMatName ?? "",
                                productId = pi.ProductID ?? "",
                                productName = pi.ProductName ?? "",
                                qty = pi.PiQTY,
                                isEnable = false
                            });
                        }
                    }
                    foreach (var deletedraft in result)
                    {
                        db.Database.ExecuteSqlCommand(string.Format("delete draftPi where branchCode = '{0}'", deletedraft.Location));
                    }

                    db.draftPis.AddRange(alldrafts);
                    return db.SaveChanges() > 0;
                }
            }
            catch (Exception e)
            {

                var k = e.Message;
                return false;
            }
            
        }

        public List<GroupDraftPiLocation> GetAllDraftPi()
        {
            using (var db = new GBHEntities())
            {
                var piOrders = (from pi in db.draftPis
                                where pi.isEnable == true
                                select pi).ToList();
                var result = piOrders.GroupBy(pi => pi.branchCode, (key, group) => new GroupDraftPiLocation()
                {
                    Location = key,
                    DraftPi = group
                }).ToList();
                var piResult = (from r in result
                                join b in db.Branches on r.Location equals b.mprovtrans
                                select new GroupDraftPiLocation()
                                {
                                    LocationNo = b.mbranchno,
                                    Location = r.Location,
                                    LocationName = b.mprovdesc,
                                    DraftPi = r.DraftPi
                                }).ToList();
                return piResult;
            }
        }

       
        public List<ExportPiDetail> GetExportPiDetails(int exportPiId)
        {
            using (var db = new GBHEntities())
            {
                var result = (from pi in db.ExportPiDetails
                              where pi.ExportId == exportPiId
                              select pi).ToList();

                return result;
            }
        }

        // Post api/ProductApi/PostGetproduct
        public material PostGetproduct(string id)
        {
            using (var db = new GBHEntities())
            {
                material product = db.materials.Find(id);
                if (product == null)
                {
                    throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
                }

                return product;
            }
        }

        // Post api/ProductApi/PostGetproduct
        public FileImportDetails GetImport()
        {
            var importDetails = new FileImportDetails();

            string root = HttpContext.Current.Server.MapPath("~/App_Data");
            if (Directory.Exists(root + "\\uploads\\Stock\\"))
            {
                var stockPath = Directory.GetFiles(root + "\\uploads\\Stock\\").FirstOrDefault();
                var lastModify = File.GetLastWriteTime(stockPath);
                var fileName = Path.GetFileName(stockPath);
                importDetails.StockPath = fileName + " " + lastModify.ToLongTimeString();
            }
            if (Directory.Exists(root + "\\uploads\\Sellout\\"))
            {
                var sellPath = Directory.GetFiles(root + "\\uploads\\Sellout\\").FirstOrDefault();
                var lastModify = File.GetLastWriteTime(sellPath);
                var fileName = Path.GetFileName(sellPath);
                importDetails.SellOutPath = fileName + " " + lastModify.ToLongTimeString();
            }
            return importDetails;
        }

        // Post api/ProductApi/PostUpdateproduct
        public HttpResponseMessage PostUpdateproduct(material product)
        {
            using (var db = new GBHEntities())
            {
                if (!ModelState.IsValid)
                {
                    return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
                }

                db.Entry(product).State = EntityState.Modified;

                try
                {
                    db.SaveChanges();
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
                }

                return Request.CreateResponse(HttpStatusCode.OK);
            }
        }

        // POST api/ProductApi/Inserproduct
        public HttpResponseMessage PostInserproduct(material product)
        {
            using (var db = new GBHEntities())
            {
                if (ModelState.IsValid)
                {
                    db.materials.Add(product);
                    db.SaveChanges();

                    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, product);
                    response.Headers.Location = new Uri(Url.Link("DefaultApi", new { id = product.mmatno }));
                    return response;
                }
                else
                {
                    return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
                }
            }
        }

        // Post api/ProductApi/PostDeleteproduct
        public HttpResponseMessage PostDelete(material productDelete)
        {
            using (var db = new GBHEntities())
            {
                material product = db.materials.Find(productDelete.mmatno);
                if (product == null)
                {
                    return Request.CreateResponse(HttpStatusCode.NotFound);
                }

                db.materials.Remove(product);

                try
                {
                    db.SaveChanges();
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
                }

                return Request.CreateResponse(HttpStatusCode.OK, product);
            }
        }

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
        }
    }
}